package util;

import java.sql.*;
import java.util.Optional;

public class DBUtil {

    private static Connection conn;
    private static PreparedStatement ps;

    private DBUtil() {}

    private static String url = "jdbc:mysql://122.112.164.115:3306/shop_admin?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    private static String username="root";
    private static String password="root";
    private static String className="com.mysql.jdbc.Driver";

    static {
        try {
            Class.forName(className);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void connect() {
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void open() {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static int executeInsertOfGeneratedKey(String sql, Object... vals) throws SQLException {
        int key = 0;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i=0;i<vals.length;i++) {
                ps.setObject(i + 1, vals[i]);
            }
            ps.execute();
            rs = ps.getGeneratedKeys();
            rs.next();
            key = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            closeOne(rs);
            return key;
        }
    }

    public static int executeUpdate(String sql, Object... vals) throws SQLException {
        try {
            ps = conn.prepareStatement(sql);
            for (int i=0;i<vals.length;i++) {
                ps.setObject(i + 1, vals[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    public static ResultSet executeQuery(String sql, Object... vals) throws SQLException {
        try {
            ps = conn.prepareStatement(sql);
            for (int i=0;i<vals.length;i++) {
                ps.setObject(i + 1, vals[i]);
            }
            return ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    public static void commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Savepoint save() throws SQLException {
        return conn.setSavepoint();
    }

    public static void rollback() {
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void rollback(Savepoint sp) {
        try {
            conn.rollback(sp);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close() {
        closeOne(ps);
        closeOne(conn);
    }

    public static void close(ResultSet rs) {
        closeOne(rs);
        closeOne(ps);
        closeOne(conn);
    }

    private static void closeOne(AutoCloseable a) {
        Optional.ofNullable(a).ifPresent(ac -> {
            try {
                ac.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
    }
}
